package cn.lxycx.dataterrace.controller;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.jfinal.aop.Before;
import com.jfinal.core.Controller;
import com.jfinal.kit.Kv;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.lxycx.dataterrace.config.jfinalConfig;
import cn.lxycx.dataterrace.interceptor.validates.DeleteValidate;
import cn.lxycx.dataterrace.interceptor.validates.ExpValidate;
import cn.lxycx.dataterrace.interceptor.validates.InsertValidate;
import cn.lxycx.dataterrace.interceptor.validates.QueryValidate;
import cn.lxycx.dataterrace.interceptor.validates.UpdateValidate;
import cn.lxycx.dataterrace.model.CurdDao;
import cn.lxycx.dataterrace.util.DbStream;
import cn.lxycx.dataterrace.util.DbStream.DataExec;
import cn.lxycx.dataterrace.util.ExcelRW;

public class CurdController extends Controller {
	
	private static Logger log = Logger.getLogger(CurdController.class);
	private String driver = PropKit.get("driver");
	
	
	public void index(){
		renderJsp("crud.jsp");
	}
	
	
	
	/**
	 * 
	 * 添加数据
	 * @author 邢超
	 * 创建时间：2017年11月23日
	 *
	 */
	@Before(InsertValidate.class)
	public void insert(){
		Map<String,String> params = this.getAttr("params");
		String tablename = this.getAttr("tablename");
		String aidfield = this.getAttr("aidfield");
		String seqstr = this.getAttr("seqstr");
		/*if(!params.containsKey(aidfield.toLowerCase())&&!params.containsKey(aidfield.toUpperCase())) {
			//没有手动插入ID
			if(seqstr!=null) {
				params.put(aidfield, "#"+seqstr);
			}else {
				this.setAttr("res", -1);
				this.setAttr("msg", "请定义序列名称");
				renderJson();
				return;
			}
		}*/
		if(params.containsKey(aidfield)){ //如果主键传入的值以nextval 结尾的，直接作为关键字解析
			String  value = params.get(aidfield);
			if(value.endsWith("nextval")){
				params.put(aidfield, "#"+value);
			}
		}

		boolean b = CurdDao.save(tablename,params);
		if(b){
			this.setAttr("res", 1);
			this.setAttr("msg", "添加成功！");
		}else{
			this.setAttr("res", -1);
			this.setAttr("msg", "添加失败！");
		}
		renderJson();
	}
	
	
	/**
	 * 
	 * 修改数据
	 * @author 邢超
	 * 创建时间：2017年11月23日
	 *
	 */
	@Before(UpdateValidate.class)
	public void update(){
		Map<String,String> params = this.getAttr("params");
		String tablename = this.getAttr("tablename");
		String aidfield = this.getAttr("aidfield");
		Map<String,String> where = this.getAttr("where");
		String fail_hint = this.getAttr("fail_hint");
		
		Kv kk = Kv.by(aidfield, this.getPara("rid"));
		kk.putAll(where);

		
		try {
			log.info("更新参数："+kk);
			boolean b = CurdDao.update(tablename, params, kk);//UpdateDao.update(tablename, params, Kv.by("id", this.getPara("rid")));//(tablename, params);
			if(b){
				this.setAttr("res", 1);
				this.setAttr("msg", "修改成功！");
			}else{
				this.setAttr("res", -1);
				this.setAttr("msg", fail_hint);
			}
		} catch (Exception e) {
			log.error("修改失败：",e);
			this.setAttr("res", -1);
			this.setAttr("msg", "修改失败："+e.getMessage());
		}
		
		renderJson();
	}
	

	/**
	 * 
	 * 删除数据
	 * @author 邢超
	 * 创建时间：2017年11月23日
	 *
	 */
	@Before(DeleteValidate.class)
	public void delete(){
		String tablename = this.getAttr("tablename");
		boolean b = CurdDao.delByField(tablename, Kv.by("id", this.getPara("rid")));//(tablename, params);
		if(b){
			this.setAttr("res", 1);
			this.setAttr("msg", "删除成功！");
		}else{
			this.setAttr("res", -1);
			this.setAttr("msg", "删除失败！");
		}
		renderJson();
	}
	
	
	
	
	
	
	
	/**
	 * 
	 * 查询数据
	 * @author 邢超
	 * 创建时间：2017年11月23日
	 *
	 */
	@Before(QueryValidate.class)
	public void findList(){
		int page = this.getParaToInt("page");
		int size = this.getParaToInt("size");
		String sql  = this.getAttr("sql");
		List<Object> values = this.getAttr("values");
		log.info("查询SQL:"+sql);
		if(sql!=null){
			Page<Record> list =Db.use("data").paginateByFullSql(page, size, "select count(1) from ("+sql+") ttt", sql, values.toArray());
					//(page, size, "select * ", "from("+sql+")",values.toArray());
					//Db.paginate(page, size, "select * ", "from("+sql+")",values.toArray());
			this.setAttr("list", list);	
		}
		renderJson();
	}
	
	/**查询总数*/
	@Before(QueryValidate.class)
	public void findCount(){
		String sql  = this.getAttr("sql");
		List<Object> values = this.getAttr("values");
		
		if(sql!=null){
			if(driver.indexOf("oracle")>0){
				this.setAttr("cou", Db.queryBigDecimal("select count(1) from ("+sql+") ttt",values.toArray()).intValue());
			}else{
				this.setAttr("cou", Db.queryInt("select count(1) from ("+sql+") ttt",values.toArray()).intValue());
			}

		}
		renderJson();
	}
	

	
	
	
	
	
	/**
	 * 按月或按号码导出业务记录
	 * */
	@Before(ExpValidate.class)
	public void expBus(){
		String sql  = this.getAttr("sql");
		List<Object> values = this.getAttr("values");
		int len = PropKit.getInt("exp_excel_len",60000);
		try {
			int count = Db.queryBigDecimal("select count(1) cou from ( "+sql+" )",values.toArray()).intValue();
			log.info("当前导出数量："+count);
			//List<Record> buss = Db.find(sql);;//ClientDao.getBus(date, phone);
			if(count<len){
				List<Record> list = CurdDao.exp(sql,values.toArray());//
				String path = this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.xls";//PropKit.get("excelpath");//this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.xls";
				File file = new File(path);
				
				ExcelRW.WriteExcel(file, list);
				renderFile(file);
				return;				
			}else{
				renderText("导出的数据不可大于"+len+"行");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error(e);
			renderText("导出失败");
		}
	}
	
	
	
	/**
	 * 按月或按号码导出业务记录
	 * */
	@Before(ExpValidate.class)
	public void expBusToCsv(){
		String sql  = this.getAttr("sql");
		List<Object> values = this.getAttr("values");
		int len = PropKit.getInt("exp_csv_len",300000);
		try {
			int count = Db.queryBigDecimal("select count(1) cou from ( "+sql+" )",values.toArray()).intValue();
			//List<Record> buss = Db.find(sql);;//ClientDao.getBus(date, phone);
			log.info("当前导出数量："+count);
			//int csvlen = PropKit.getInt("csvlen",100000);
			if(count<len){
				List<Record> list = CurdDao.exp(sql,values.toArray());//
				String path = this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.csv";//PropKit.get("excelpath");//this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.xls";
				File file = new File(path);
				//file.mkdirs();
				//ExcelRW.WriteExcel(file, list);
				ExcelRW.WriteCsv(file, list);
				renderFile(file);
				return;
				
			}else{
				renderText("导出数据不可大于"+len+"行");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error(e);
			renderText("导出失败");
		}
	}
	
	/**
	 * 按月或按号码导出业务记录
	 * */
	@Before(ExpValidate.class)
	public void expBusToTxt(){
		String sql  = this.getAttr("sql");
		List<Object> values = this.getAttr("values");
		//int len = PropKit.getInt("exp_csv_len",300000);
		
		log.info("sql:"+sql+"---values:"+values);
		
		HttpServletResponse resp = this.getResponse();
		resp.setHeader("Content-disposition", "attachment;filename=datalist.txt");
		try {
			
			/*String path = this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.csv";//PropKit.get("excelpath");//this.getClass().getResource("/").getPath().replace("/classes", "")+"upload/excel.xls";
			File file = new File(path);*/
			
		//	final BufferedWriter br = new BufferedWriter(resp.getWriter());//new BufferedWriter(new FileWriter(file));
			final BufferedOutputStream bout = new BufferedOutputStream(resp.getOutputStream());
			
			DbStream.findList(sql, new DataExec() {
				@Override
				public void exec(Map<String,Object> map,int index) throws IOException {
					System.out.println("map:"+map);
					if(index==1) {//首行显示名称
						String names =StringUtils.join(map.keySet(),",")+"\r\n";
						bout.write(names.getBytes("GBK"));
					}
					
					Object[] Obj = map.values().toArray();
					String value =StringUtils.join(Obj,",")+"\r\n";
					bout.write(value.getBytes("GBK"));
					
					//一万条数据刷新一次
					if(index%10000==0) {
						bout.flush();
						log.info("刷新数据："+index);
					}
				}
			},values.toArray());
			
			bout.close();
			//renderFile(file);
			renderNull();
			return;
		} catch (Exception e) {
			log.error(e);
			renderText("导出失败");
		}
	}
	
	
	
	
	
	
	
	
	public static void main(String[] args) {
		int page=1,size=20;
		String sql = "select id,classname from data_statement_class order by id";
		jfinalConfig.mainInit();
		Page<Record> list =Db.paginateByFullSql(page, size, "select count(1) from ("+sql+")", sql);
		List<Record> relist = Db.find(sql);
		System.out.println(list.getList());
		System.out.println(relist);
	}
	
	
	

}
